/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import entity.*;
import utils.DBHelper;

/**
 * @author Chen Lei
 *
 */
public class ThemeDAO {
	private ThemeDAO() {
	}

	public static ThemeDAO getInstance() {
		return new ThemeDAO();
	}
	/**
	 * 得到系统主题总数
	 *
	 */
	public int getTotal() {
		int total = 0;
		try {

			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select count(*) from theme";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}

			System.out.println("total:" + total);

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return total;
	}
	/**
	 * 新增主题
	 *
	 */
	public void add(Theme theme) {

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "insert into theme values(null,?)";
			PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		
			ps.setString(1, theme.getTheme_name());
			
			ps.execute();
			
			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int theme_id =  rs.getInt(1);
				theme.setTheme_id(theme_id);
			}

			DBHelper.closeConnection(c, ps, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 修改主题名称
	 *
	 */
	public void update(Theme theme) {
		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "update theme set theme_name= ? where theme_id = " + theme.getTheme_id();
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, theme.getTheme_name());

			ps.execute();
		
			DBHelper.closeConnection(c, ps, null);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 删除主题，该主题下的帖子主题更换为"其它"
	 *
	 */
	public void delete(int theme_id) {
		try {

			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "delete from theme where theme_id = " + theme_id;

			s.execute(sql);

			DBHelper.closeConnection(c, s, null);

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 通过主题id得到主题对象
	 *
	 */
	public Theme get(int theme_id) {
		Theme theme = null;
		try {
			
			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select * from theme where theme_id = " + theme_id;

			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {

				theme = new Theme();
				
				String theme_name = rs.getString("theme_name");
				
				theme.setTheme_id(theme_id);
				theme.setTheme_name(theme_name);
				
			}

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return theme;
	}
	
	/**
	 * 通过主题id得到主题名称
	 *
	 */
	public String get_theme_name(int theme_id) {
		String theme_name = "";
		try {
			
			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select * from theme where theme_id = " + theme_id;

			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {
				theme_name = rs.getString("theme_name");
			}

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return theme_name;
	}
	
	
	/**
	 * 得到主题列表
	 *
	 */
	public List<Theme> list() {
		return list(0, Short.MAX_VALUE);
	}
	/**
	 * 得到从start开始count个主题
	 *
	 */
	public List<Theme> list(int start, int count) {
		List<Theme> themes = new ArrayList<Theme>();

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "select * from theme order by theme_id limit ?,? ";

			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Theme theme = new Theme();
				int theme_id = rs.getInt("theme_id");
				String theme_name = rs.getString("theme_name");
			
				theme.setTheme_id(theme_id);
				theme.setTheme_name(theme_name);
				
				themes.add(theme);
			}
			DBHelper.closeConnection(c, ps, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return themes;
	}
	/**
	 * 得到所有的
	 * @return
	 */
	public List<Theme> getAll(){
		List<Theme> themelist = new ArrayList<>();
		try {
			Theme theme = null;
			Connection c = DBHelper.getInstance().getConnection();

			Statement s = c.createStatement();

			String sql = "select * from theme";

			ResultSet rs = s.executeQuery(sql);

			while (rs.next()) {

				theme = new Theme();
				int theme_id = rs.getInt("theme_id");
				String theme_name = rs.getString("theme_name");				
				theme.setTheme_id(theme_id);
				theme.setTheme_name(theme_name);
				themelist.add(theme);
			}

			DBHelper.closeConnection(c, s, rs);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return themelist;
		
	}
}
